package dao;

import beans.Animal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class AnimalDAO {

    PreparedStatement pst;
    ResultSet rs;

    public AnimalDAO() {
    }

    public Animal load(int ano, int mes, int numBrinco) {
        Animal anml = new Animal();

        String query = "SELECT * FROM animal WHERE ano = ? AND mes = ? AND num_brinco = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, ano);
            pst.setInt(2, mes);
            pst.setInt(3, numBrinco);

            rs = pst.executeQuery();

            if (rs.next()) {
                anml.setAno(rs.getInt("ano"));
                anml.setMes(rs.getInt("mes"));
                anml.setNumBrinco(rs.getInt("numBrinco"));
                anml.setRaca(rs.getString("raca"));
                anml.setPeso(rs.getFloat("peso"));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        return anml;
    }
	
	    public ArrayList<Animal> loadRebanho(int mes, int ano) {
        ArrayList<Animal> animais = new ArrayList<Animal>();

        String query = "SELECT ano, mes, num_brinco, raca, peso FROM animal WHERE ano = ? AND mes = ?";
        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, ano);
            pst.setInt(2, mes);
                                   
            rs = pst.executeQuery();

            while (rs.next()) {
                Animal an = new Animal();

                an.setAno(rs.getInt("ano"));
                an.setMes(rs.getInt("mes"));
                an.setNumBrinco(rs.getInt("num_brinco"));
                an.setRaca(rs.getString("raca"));
                an.setPeso(rs.getFloat("peso"));

                animais.add(an);
            }
        } catch (SQLException ex) {
            ex.getLocalizedMessage();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }

        return animais;
    }

    public ArrayList<Animal> loadTourosRebanho(int mes, int ano) {
        ArrayList<Animal> animais = new ArrayList<Animal>();

        String query = "SELECT ano, mes, num_brinco, raca, peso FROM animal WHERE ano = ? AND mes = ?"
                        + " EXCEPT"
                        + " SELECT ano, mes, num_brinco, raca, peso from vaca NATURAL JOIN animal WHERE ano = ? AND mes = ?"
                        + " EXCEPT"
                        + " SELECT ano, mes, num_brinco, raca, peso from naoadulto NATURAL JOIN animal WHERE ano = ? AND mes = ?";
                
        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, ano);
            pst.setInt(2, mes);
            pst.setInt(3, ano);
            pst.setInt(4, mes);
            pst.setInt(5, ano);
            pst.setInt(6, mes);
                                   
            rs = pst.executeQuery();

            while (rs.next()) {
                Animal an = new Animal();

                an.setAno(rs.getInt("ano"));
                an.setMes(rs.getInt("mes"));
                an.setNumBrinco(rs.getInt("num_brinco"));
                an.setRaca(rs.getString("raca"));
                an.setPeso(rs.getFloat("peso"));

                animais.add(an);
            }
        } catch (SQLException ex) {
            ex.getLocalizedMessage();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }

        return animais;
    }

    public void insert(Animal anml) {
        String query = "INSERT INTO animal (ano, mes, num_brinco, raca, peso)"
                + " VALUES(?, ?, ?, ?, ?)";

        try {
            pst = Conexao.getConexao().prepareStatement(query);

            pst.setInt(1, anml.getAno());
            pst.setInt(2, anml.getMes());
            pst.setInt(3, anml.getNumBrinco());
            pst.setString(4, anml.getRaca());
            pst.setFloat(5, anml.getPeso());
			
			System.out.println(pst);
			
            pst.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    private void update(Animal anml) {
        String query = "UPDATE animal"
                + "SET ano = ?, mes = ?, raca = ?, peso = ? "
                + "WHERE numBrinco = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);

            pst.setInt(1, anml.getAno());
            pst.setInt(2, anml.getMes());
            pst.setString(3, anml.getRaca());
            pst.setFloat(4, anml.getPeso());
            pst.setInt(5, anml.getNumBrinco());

            pst.executeUpdate();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }


    }

    public void save(Animal anml) {
        String query = "SELECT COUNT(*) FROM animal WHERE ano = ? AND mes = ? AND numBrinco = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, anml.getAno());
            pst.setInt(2, anml.getMes());
            pst.setInt(3, anml.getNumBrinco());

            rs = pst.executeQuery();
            rs.next();

            if (rs.getInt("count") != 0) {
                insert(anml);
            } else {
                update(anml);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }
    }

    public void delete(Animal anml) {
        String query = "DELETE FROM animal WHERE ano = ? AND mes = ? AND numBrinco = ?";

        try {
            pst = Conexao.getConexao().prepareStatement(query);
            pst.setInt(1, anml.getAno());
            pst.setInt(2, anml.getMes());
            pst.setInt(3, anml.getNumBrinco());

            pst.executeUpdate();

        } catch (SQLException ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                ex.getLocalizedMessage();
            }
        }
    }

}
